const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🗑️ 删除带中文和分类码的实例表');
console.log('===================================');

try {
  const db = new Database(DB_PATH);
  
  console.log('\n🔍 分析当前实例表...');
  
  // 查找所有实例表
  const allInstanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 当前实例表总数: ${allInstanceTables.length} 个`);
  
  // 分类表格：需要删除的表（含中文或分类码）和需要保留的表
  const tablesToDelete = [];
  const tablesToKeep = [];
  
  for (const table of allInstanceTables) {
    const tableName = table.name;
    
    // 检查是否包含中文字符
    const hasChinese = /[\u4e00-\u9fa5]/.test(tableName);
    
    // 检查是否包含分类码模式（如 L4_06_01_01_01_000）
    const hasClassificationCode = /L\d+_\d+_\d+_\d+_\d+_\d+/.test(tableName);
    
    if (hasChinese || hasClassificationCode) {
      tablesToDelete.push(tableName);
    } else {
      tablesToKeep.push(tableName);
    }
  }
  
  console.log(`\n📊 分析结果:`);
  console.log(`  🗑️ 需要删除的表: ${tablesToDelete.length} 个`);
  console.log(`  ✅ 需要保留的表: ${tablesToKeep.length} 个`);
  
  // 显示需要删除的表（带数据统计）
  console.log(`\n🗑️ 需要删除的表列表:`);
  let totalDataToDelete = 0;
  
  for (const tableName of tablesToDelete) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${tableName}]`).get();
      totalDataToDelete += count.count;
      
      if (count.count > 0) {
        console.log(`  📊 ${tableName}: ${count.count} 条记录 (有数据)`);
      } else {
        console.log(`  📋 ${tableName}: 0 条记录 (空表)`);
      }
    } catch (error) {
      console.log(`  ❌ ${tableName}: 查询失败 - ${error.message}`);
    }
  }
  
  // 显示需要保留的表
  console.log(`\n✅ 需要保留的表列表:`);
  let totalDataToKeep = 0;
  
  for (const tableName of tablesToKeep) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${tableName}]`).get();
      totalDataToKeep += count.count;
      
      if (count.count > 0) {
        console.log(`  📊 ${tableName}: ${count.count} 条记录`);
      } else {
        console.log(`  📋 ${tableName}: 0 条记录`);
      }
    } catch (error) {
      console.log(`  ❌ ${tableName}: 查询失败 - ${error.message}`);
    }
  }
  
  console.log(`\n📊 数据影响评估:`);
  console.log(`  将删除数据记录: ${totalDataToDelete} 条`);
  console.log(`  将保留数据记录: ${totalDataToKeep} 条`);
  
  if (totalDataToDelete > 0) {
    console.log(`\n⚠️  警告: 即将删除 ${totalDataToDelete} 条实例数据记录！`);
  }
  
  // 执行删除操作
  console.log(`\n🗑️ 开始删除操作...`);
  
  let deletedCount = 0;
  let errorCount = 0;
  
  for (const tableName of tablesToDelete) {
    try {
      db.prepare(`DROP TABLE IF EXISTS [${tableName}]`).run();
      deletedCount++;
      console.log(`  ✅ 已删除: ${tableName}`);
    } catch (error) {
      errorCount++;
      console.log(`  ❌ 删除失败: ${tableName} - ${error.message}`);
    }
  }
  
  // 最终验证
  console.log(`\n📊 删除操作完成`);
  console.log(`====================`);
  console.log(`✅ 成功删除: ${deletedCount} 个表`);
  console.log(`❌ 删除失败: ${errorCount} 个表`);
  
  // 验证当前状态
  const remainingTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 删除后剩余实例表: ${remainingTables.length} 个`);
  
  let remainingData = 0;
  console.log(`\n📋 剩余实例表详情:`);
  
  for (const table of remainingTables) {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      remainingData += count.count;
      
      if (count.count > 0) {
        console.log(`  📊 ${table.name}: ${count.count} 条记录`);
      } else {
        console.log(`  📋 ${table.name}: 0 条记录`);
      }
    } catch (error) {
      console.log(`  ❌ ${table.name}: 查询失败`);
    }
  }
  
  console.log(`\n🎉 删除操作汇总:`);
  console.log(`  删除表数量: ${deletedCount} 个`);
  console.log(`  保留表数量: ${remainingTables.length} 个`);
  console.log(`  保留数据记录: ${remainingData} 条`);
  
  if (deletedCount > 0) {
    console.log(`\n✅ 成功删除了带中文和分类码的实例表`);
    console.log(`💡 建议: 如果需要恢复，请使用备份文件`);
  }
  
  db.close();
  
} catch (error) {
  console.error('❌ 删除操作失败:', error.message);
  console.error(error.stack);
}